﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for MemberInfo
/// </summary>
public class MemberInfo
{
    public MemberInfo()
    {
        //
        // TODO: Add constructor logic here
        //
    }


    private static string GetConnectionString()
    {
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["BalloonShopConnection"].ConnectionString;
    }


    public static void ExecuteInsert(string fName, string lName, string gender, int age, string stAddress, string prov, string city, string postal, string phone, string email,
        string pwd)
    {

        SqlConnection conn = new SqlConnection(GetConnectionString());

        string sql = "INSERT INTO CustomerInfo (FirstName, LastName, Gender, Age, StreetAddress, ProvCode, City, PostalCode, PhoneNumber, Email, Password) VALUES "
                    + " (@FName,@LName,@Gender,@Age,@StreetAddress, @ProvCode, @City, @Postal, @Phone, @Email, @Pwd)";

        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlParameter[] param = new SqlParameter[11];
            //param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
            param[0] = new SqlParameter("@FName", SqlDbType.NVarChar, 50);
            param[1] = new SqlParameter("@LName", SqlDbType.NVarChar, 50);
            param[2] = new SqlParameter("@Gender", SqlDbType.NVarChar, 10);
            param[3] = new SqlParameter("@Age", SqlDbType.Int);
            param[4] = new SqlParameter("@StreetAddress", SqlDbType.NVarChar, 100);
            param[5] = new SqlParameter("@ProvCode", SqlDbType.NVarChar, 3);
            param[6] = new SqlParameter("@City", SqlDbType.NVarChar, 50);
            param[7] = new SqlParameter("@Postal", SqlDbType.NVarChar, 7);
            param[8] = new SqlParameter("@Phone", SqlDbType.NVarChar, 50);
            param[9] = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
            param[10] = new SqlParameter("@Pwd", SqlDbType.NVarChar, 50);


            param[0].Value = fName;
            param[1].Value = lName;
            param[2].Value = gender;
            param[3].Value = age;
            param[4].Value = stAddress;
            param[5].Value = prov;
            param[6].Value = city;
            param[7].Value = postal;
            param[8].Value = phone;
            param[9].Value = email;
            param[10].Value = pwd;


            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            conn.Close();
        }
    }


    public static int CheckUserEmail(string userEmail)
    {
        SqlConnection con = new SqlConnection(GetConnectionString());
        string query = "SELECT COUNT(Email) AS UserEmail FROM CustomerInfo WHERE Email= @UserEmail";


        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlParameter param = new SqlParameter("@UserEmail", SqlDbType.NVarChar, 50);
            param.Value = userEmail;
            cmd.Parameters.Add(param);
            cmd.CommandType = CommandType.Text;
            int rows = Convert.ToInt32(cmd.ExecuteScalar());
            return rows;
        }
        finally
        {
            con.Close();
        }
    }


    public static object[] getUserInfo(string userEmail)
    {
        SqlConnection con = new SqlConnection(GetConnectionString());
        string query = "SELECT FirstName, LastName, Gender, Age, StreetAddress, ProvCode, City, PostalCode, PhoneNumber, Email FROM CustomerInfo WHERE Email= @UserEmail";
        object[] userInfo;

        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@UserEmail", userEmail);
            SqlDataReader reader = cmd.ExecuteReader();
            userInfo = new object[10] { "", "", "", "", "", "", "", "", "", "" };

            while (reader.Read())
            {
                userInfo[0] = reader[0].ToString();
                userInfo[1] = reader[1].ToString();
                userInfo[2] = reader[2].ToString();
                userInfo[3] = reader[3].ToString();
                userInfo[4] = reader[4].ToString();
                userInfo[5] = reader[5].ToString();
                userInfo[6] = reader[6].ToString();
                userInfo[7] = reader[7].ToString();
                userInfo[8] = reader[8].ToString();
                userInfo[9] = reader[9].ToString();
                //userInfo[10] = reader[10].ToString();

            }

            reader.Close();
            ((IDisposable)reader).Dispose();

            return userInfo;
        }
        finally
        {
            con.Close();
        }
    }


    public static int CheckLoginInfo(string userEmail, string userPwd)
    {
        SqlConnection conStr = new SqlConnection(GetConnectionString());
        string query = "SELECT COUNT(*) FROM CustomerInfo WHERE Email= @UserEmail AND Password=@pwd";


        try
        {
            conStr.Open();
            SqlCommand cmd = new SqlCommand(query, conStr);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@UserEmail", SqlDbType.NVarChar, 50);
            param[1] = new SqlParameter("@pwd", SqlDbType.NVarChar, 50);

            param[0].Value = userEmail;
            param[1].Value = userPwd;

            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }
            cmd.CommandType = CommandType.Text;
            int rows = Convert.ToInt32(cmd.ExecuteScalar());
            return rows;
        }
        finally
        {
            conStr.Close();
        }
    }

    public static void ExecuteUpdate(string fName, string lName, string gender, int age, string stAddress, string prov, string city, string postal, string phone,
        string pwd, string email)
    {

        SqlConnection conn = new SqlConnection(GetConnectionString());

        string sql = "UPDATE CustomerInfo SET FirstName=@FName, LastName=@LName, Gender= @Gender, Age=@Age, StreetAddress= @StreetAddress, ProvCode= @ProvCode, "
        + " City= @City, PostalCode= @Postal, PhoneNumber= @Phone, Password= @Pwd WHERE (Email= @Email)";

        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlParameter[] param = new SqlParameter[11];

            param[0] = new SqlParameter("@FName", SqlDbType.NVarChar, 50);
            param[1] = new SqlParameter("@LName", SqlDbType.NVarChar, 50);
            param[2] = new SqlParameter("@Gender", SqlDbType.NVarChar, 10);
            param[3] = new SqlParameter("@Age", SqlDbType.Int);
            param[4] = new SqlParameter("@StreetAddress", SqlDbType.NVarChar, 100);
            param[5] = new SqlParameter("@ProvCode", SqlDbType.NVarChar, 3);
            param[6] = new SqlParameter("@City", SqlDbType.NVarChar, 50);
            param[7] = new SqlParameter("@Postal", SqlDbType.NVarChar, 7);
            param[8] = new SqlParameter("@Phone", SqlDbType.NVarChar, 50);
            param[9] = new SqlParameter("@Pwd", SqlDbType.NVarChar, 50);
            param[10] = new SqlParameter("@Email", SqlDbType.NVarChar, 50);


            param[0].Value = fName;
            param[1].Value = lName;
            param[2].Value = gender;
            param[3].Value = age;
            param[4].Value = stAddress;
            param[5].Value = prov;
            param[6].Value = city;
            param[7].Value = postal;
            param[8].Value = phone;
            param[9].Value = pwd;
            param[10].Value = email;


            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Update Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            conn.Close();
        }
    }

}

//userInfo{ reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3], reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString(), reader[9].ToString(), reader[10].ToString() };

/*SqlParameter param = new SqlParameter("@UserEmail", SqlDbType.NVarChar, 50);
            param.Value = userEmail;
            cmd.Parameters.Add(param);
            cmd.CommandType = CommandType.Text;*/